Amazon Redshift Spectrum INSERT INTOでパーティションテーブル作成を試してみました

データアナリティクス事業本部の石川です。数週間前にAmazon Redshift Spectrum のCTASとINSERT INTOをサポートのブログを書きました。今回はその機能を用いて、データがパーティション分割されて、かつ自動的にパーティション設定する機能について解説します。

CTASとINSERT INTOのサポートについては、以下のブログをご覧ください。

以降では、パーティション設定した外部テーブルを作成した後、そのテーブルにデータをディープコピー（INSERT INTO）して動作を解説します。

パーティション設定した外部テーブルの作成

出力先の外部テーブルのデータファイルフォーマットはparquetに指定、パーティションキーはorder_dateカラムを指定して外部テーブルを作成しました。パーティション設定したテーブルは、order_dateカラムをコメント化した代わりに、partitioned by (order_date varchar(10))とパーティションキーを指定します。

cmdb=# create external table cm_user_db.order_partitioned(
cmdb(# row_id bigint,
cmdb(# order_id varchar(32),
cmdb(# -- order_date varchar(10),
cmdb(# ship_date varchar(10),
cmdb(# ship_mode varchar(64),
cmdb(# customer_id varchar(64),
cmdb(# customer_name varchar(64),
cmdb(# segment varchar(64),
cmdb(# country varchar(16),
cmdb(# city varchar(16),
cmdb(# state varchar(16),
cmdb(# region varchar(16),
cmdb(# product_id varchar(255),
cmdb(# category varchar(16),
cmdb(# sub_category varchar(32),
cmdb(# product_name varchar(255),
cmdb(# sales double precision,
cmdb(# quantity bigint,
cmdb(# discount double precision,
cmdb(# profit double precision)
cmdb-# partitioned by (order_date varchar(10))
cmdb-# stored as parquet
cmdb-# location 's3://cm-user/order_partitioned/'
cmdb-# ;
CREATE EXTERNAL TABLE

データのディープコピー（INSERT INTO）

INSERT INTOで外部テーブルにディープコピー（INSERT INTO）します。パーティションに指定したカラムは、一番最後のカラムの後ろに追加されるので、order_dateを最後のカラムに指定しています。

cmdb=# insert into cm_user_db.order_partitioned
cmdb-# select
cmdb-# row_id,
cmdb-# order_id,
cmdb-# -- order_date,
cmdb-# ship_date,
cmdb-# ship_mode,
cmdb-# customer_id,
cmdb-# customer_name,
cmdb-# segment,
cmdb-# country,
cmdb-# city,
cmdb-# state,
cmdb-# region,
cmdb-# product_id,
cmdb-# category,
cmdb-# sub_category,
cmdb-# product_name,
cmdb-# sales,
cmdb-# quantity,
cmdb-# discount,
cmdb-# profit,
cmdb-# order_date
cmdb-# from cm_user_db.order;
SELECT

1239パーティションの分割に約４分間で完了しました。

結果の確認

INSERT INTOでデータをディープコピーしただけでパーティションが設定されていること、レコード数に相違がないことが確認できました。TBLPROPERTIESのnumRowsを確認したところ追加したレコードの件が更新されています。

cmdb=# select * from cm_user_db.order_partitioned limit 1;
 row_id |    order_id     | ship_date  | ship_mode | customer_id | customer_name |   segment    | country | city | state |  region  |      product_id      | category | sub_category |        product_name         | sales | quantity | discount | profit | order_date
--------+-----------------+------------+-----------+-------------+---------------+--------------+---------+------+-------+----------+----------------------+----------+--------------+-----------------------------+-------+----------+----------+--------+------------
    244 | JP-2014-1116816 | 2014-09-15 | 通常配送  | 城優-17800  | 城川 優太     | 小規模事業所 | 日本    | 尼崎 | 兵庫  | 関西地方 | 事務用-バイ-10000851 | 事
務用品 | バインダー   | アコ バインダー, リサイクル |  6204 |        6 |        0 |   1548 | 2014-09-10
(1 row)


cmdb=# select count(*) from cm_user_db.order_partitioned;
 count
-------
 10000
(1 row)

cmdb=# select count(*) from cm_user_db.order;
 count
-------
 10000
(1 row)

外部テーブルのフォルダを確認すると、カラム名ありのパーティションフォルダが1239フォルダ生成されています。なお、パーティションごとのフォルダの中には、スライスの毎にparquetファイルが出力されていました。

$ aws s3 ls s3://cm-user/order_partitioned/
                           PRE order_date=2014-01-01/
                           PRE order_date=2014-01-02/
                           PRE order_date=2014-01-03/
                           PRE order_date=2014-01-04/
                           PRE order_date=2014-01-05/
                           :
                           :
                           PRE order_date=2017-12-29/
                           PRE order_date=2017-12-30/
                           PRE order_date=2017-12-31/                        

最後に

パーティション設定した外部テーブルを事前に作成して、その外部テーブルにデータをディープコピー（INSERT INTO）するだけで、パーティションの分割〜パーティションの設定〜Parquetファイルの変換まで一気に実行できるので、Glueと比較してジョブのフローがかなりシンプルになるはずです。

今回の検証では、ソースの出力先の外部テーブルのフォーマットをparquetに指定しました。最初はヘッダ行付きのTAB区切りファイル出力で試しましたが、元のテーブルのレコード数と一致しない問題が生じました。出力ファイルされたファイルを確認したところ、ヘッダ行が出力されていませんないため、先頭のレコードがスキップされたのでレコード数が少なく見えていました。

本日は弊社の誕生日（創立記念日）ですが、出来て当たり前だと思うことでも、実際やってみないとわからないと再認識しましたので、今後も試してみたブログを書き続けたいと思います。